


-- Project: SQL Introduction to Tables #2
-- Description: 
	-- This is the #2 Tables in SQL.
	-- I will create a table in SQL with 20 records of data of customers and their information.
	-- This program consists of new commands, such as create index and on.
	-- I will create their table using other commands, known as create database, use, insert into, values, and more.
	-- I will create a table for the customer, so they are able to organize their data in this table.
-- Jamila Kandawala
-- Date: 10/21/2024




create database customer; -- This has a specific command known as, create database, which is used to create a database. The name I gave it was customer.

use customer; -- This shows that I will use the database and will put the following statements in this database, such as creating the table.

-- This will allow me to create a table in the customer database, called customerData. This database consists of 9 fields.
create table customerData(
	id int primary key auto_increment, -- This will make a primary key, which will be made auto-increment that means it has no value.
	-- The majority of these fields use varchar, which is known as a type of field, and a data type, which is a string.
	firstname varchar(20),	-- This will create a field named firstname, which will consists of 20 characters.
	lastname varchar(20), -- This will create a field named lastname, which will consists of 20 characters.
	street varchar(50), -- This will create a field named street, which will consists of 50 characters.
	city varchar(20), -- This will create a field named city, which will consists of 20 characters.
	theState varchar(2), -- This will create a field named the State, which will consists of 2 characters. This will be 2 characters only because it will be an abbreviation.
	zipCode varchar(5), -- This will create a field named zipCode, which will consists of 5 characters.
	birthDate date, -- This will create a field named birthdate, which will be the date type, so it does not need a character number because it will be in the format YY-MM-DD.
	phoneNumber varchar(12) -- This will create a field named phoneNumber, which will consists of 10 characters.
);


-- This is showing that I am creating an index for the lastname column in the customerData table, which will allow it to speed up the searches, but it will not change how the table looks, but it will show you in the Key column as aq Mul, which means that it is not unique. 
create index idx_lastname on customerData(lastname); 

-- This is making an index for the column named the State in the customerData table, which will also speed up the searches. In the key column of the other table it will show it as a Mul, which means that it is not unique.
create index idx_theState on customerData(theState); 


-- This will show you the structure of the table and help you understand how the data fits into each column. This is important because it will help you put the data and columns together into a table.
describe customerData; 

-- This will make all the data and columns be listed and inserted into to the data, which will include the data below.
insert into customerData 
(firstname, lastname, street, city, theState, zipCode, birthDate, phoneNumber) -- This is the order of the fields that the data will be in, which will keep it organized and easy to understand.
-- These values are the data that will be inputted into the table, which show the format above. 
values 
('Stella', 'Handy', '300 Brooklyn Avenue', 'Brooklyn', 'NY', '11213', '2000-03-12', '203-555-5380'), -- This is the data of the customer named Stella in the format of the data above. 
('Charlie', 'McKenny', '97 Macarthur Ave', 'Garfield', 'NJ', '07026', '1990-02-01', '203-776-9087'), -- This is the data of the customer named Charlie in the format of the data above. 
('Mary', 'Lokeys', '855 Stratford Dr', 'Encinitas', 'CA', '92024', '2005-12-18', '475-875-0925'), -- This is the data of the customer named Mary in the format of the data above. 
('Steven', 'Heart', '224 Southwind Road', 'Mineral Wells', 'TX', '76067', '1986-06-04', '997-095-9835'), -- This is the data of the customer named Steven in the format of the data above. 
('Mark', 'Carrels', '4420 Norfleet St', 'Concord', 'NC', '28025', '2001-04-19', '475-986-8367'), -- This is the data of the customer named Mark in the format of the data above. 
('Mia', 'Lombroso', '10018 Evergreen Dr', 'Elberton', 'GA', '30635', '1989-09-01', '203-984-9984'), -- This is the data of the customer named Mia in the format of the data above. 
('Harry', 'Lain', '408 Juke Line Road', 'Warm Springs', 'GA', '31830', '1969-11-15', '203-987-0937'), -- This is the data of the customer named Harry in the format of the data above. 
('Stephen', 'Fisher', '428 Westover Road', 'Big Spring', 'TX', '79720', '2004-08-22', '993-834-3493'), -- This is the data of the customer named Stephen in the format of the data above. 
('Garp', 'Smith', '1504 East Blvd', 'Glassbro', 'NJ', '08028', '2001-09-29', '475-973-3822'), -- This is the data of the customer named Garp in the format of the data above. 
('Ariana', 'Brown', '2055 Montgomery Road', 'Auburn', 'KY', '42206', '1992-07-05', '203-985-3844'), -- This is the data of the customer named Ariana in the format of the data above. 
('Sofie', 'Johnson', '220 Federal Avenue', 'Morgan City', 'LA', '70380', '1980-02-12', '203-986-1734'), -- This is the data of the customer named Sofie in the format of the data above. 
('Christopher', 'Cooper', '954 Washington Road', 'Rye', 'NH', '03870', '2001-12-21', '475-986-0274'), -- This is the data of the customer named Christopher in the format of the data above. 
('William', 'Brooks', '12510 Ryerson Avenue', 'Downey', 'CA', '90242', '1995-02-13', '475-383-1851'), -- This is the data of the customer named William in the format of the data above. 
('John', 'Colton', '102 Lewey Stone Ct', 'Cary', 'NC', '27519', '1985-10-16', '203-893-3234'), -- This is the data of the customer named John in the format of the data above. 
('James', 'Miller', '9775 Clinton Road', 'Manchester', 'MI', '48158', '1973-06-28', '203-746-7374'), -- This is the data of the customer named James in the format of the data above. 
('Jake', 'Martinez', '814 Sagebrush Dr', 'Worland', 'WY', '82401', '1992-04-16', '475-947-0582'), -- This is the data of the customer named Jake in the format of the data above. 
('Weldon', 'Armstrong', '2165 Mullet Lake Park Road', 'Geneva', 'FL', '32732', '2000-07-03', '478-484-3892'), -- This is the data of the customer named Weldon in the format of the data above. 
('Luca', 'Monroe', '16 Bittersweet Ln', 'Harwich', 'MA', '02645', '2001-12-25', '284-734-3932'), -- This is the data of the customer named Luca in the format of the data above. 
('Andrew', 'Garfield', '1230 Sheffield Road', 'Cuba', 'AL', '36907', '1998-01-28', '203-485-3857'), -- This is the data of the customer named Andrew in the format of the data above. 
('Michael', 'Santiago', '412 Walker Street', 'Waynesboro', 'TN', '38485', '1987-09-26', '475-485-3948') -- This is the data of the customer named Michael in the format of the data above. 
; 

SELECT * FROM customerData; -- This will select all the data from the table called, customerData.
drop database customer; -- This will delete the database called, customer, which will also delete anything that is inside it, so the table called customerData will be deleted too.









